﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data;
using System.Text;
//using System.Windows.Forms;
using System.Net;
using System.Xml;
using System.Configuration;
using EveAI.Live;

namespace EVEMap
{

    public class eveMapDB
    {
        private string m_ConnectionString;
        private DataSet m_EVEDumpDataSet;
        private SqlConnection m_SQLConn;
        private EveApi m_API;
        private SqlCommand m_SystemLookupFromStation;

        public eveMapDB(string ConnString)
        {
            //m_ConnectionString = "Server=.\\SQLEXPRESS;database=master;Trusted_Connection=true";
            //m_ConnectionString = ConfigurationManager.ConnectionStrings["eveDBConnectionString"].ConnectionString;
            m_ConnectionString = ConnString;
//            m_API = new EveApi(2117196, "pGMTY8V7NqDJ9iXOYBvEP2zujHEq9u8oiYmthVAnfQ48NQfZcY6vB7VKZXdJEa2Y");

        }

        public void AddAssetsToMap(eveMap mapSolarSystems, List<eveSolarSystem> regionList)
        {
            SqlConnection assetsConn = new SqlConnection(m_ConnectionString);
            assetsConn.Open();
            DataSet assetsData = new DataSet("AssetsData");
            using (SqlDataAdapter assetsAdapter = new SqlDataAdapter("select * from evestuff.dbo.ScratchAssets where " +
                // hack because suddenly not all assets are in 1 region anymore
                "SolarSystemID in (select solarSystemID from ebs_DATADUMP.dbo.mapSolarSystems where regionID = 10000042)", assetsConn))
            {
                assetsAdapter.Fill(assetsData, "Assets");
            }
            using (SqlDataAdapter adapt = new SqlDataAdapter("select * from ebs_DATADUMP.dbo.mapSolarSystems where " +
                "regionID = (select regionID from ebs_DATADUMP.dbo.mapRegions where regionName = @RegionName)", assetsConn))
            {
                adapt.SelectCommand.Parameters.AddWithValue("@RegionName", "Metropolis");
                adapt.Fill(assetsData, "Systems");
            }
            assetsData.Relations.Add("assetStacks", assetsData.Tables["Systems"].Columns["solarSystemID"],
                assetsData.Tables["Assets"].Columns["SolarSystemID"]);
            using (SqlDataAdapter adapt = new SqlDataAdapter("select * from ebs_DATADUMP.dbo.staStations where " +
                "regionID = (select regionID from ebs_DATADUMP.dbo.mapRegions where regionName = @RegionName)", assetsConn))
            {
                adapt.SelectCommand.Parameters.AddWithValue("@RegionName", "Metropolis");
                adapt.Fill(assetsData, "Stations");
            }
            assetsData.Relations.Add("sysStations", assetsData.Tables["Systems"].Columns["solarSystemID"],
                assetsData.Tables["Stations"].Columns["solarSystemID"]);

            assetsData.Relations.Add("stationAssets", assetsData.Tables["Stations"].Columns["stationID"],
                assetsData.Tables["Assets"].Columns["StationID"]);
            

            foreach (DataRow systemRow in assetsData.Tables["Systems"].Rows)
            {
                int systemID = Convert.ToInt32(systemRow["solarSystemID"]);
                eveSolarSystem solarSystem = mapSolarSystems[systemID];
                solarSystem.AddStations(systemRow);
                if (!(solarSystem.AssetsValue > 0))
                    continue;
                int idEnd = mapSolarSystems["HEK"].ID;
                int idStart = solarSystem.ID;
                solarSystem.Route = mapSolarSystems.Djikstra(idStart, idEnd);
                regionList.Add(solarSystem);
            }
            regionList.Sort((x, y) => y.AssetsValue.CompareTo(x.AssetsValue));
        }

        public bool connect()
        {
            m_EVEDumpDataSet = new DataSet();
            m_SQLConn = new SqlConnection(m_ConnectionString);
            
            m_SQLConn.Open();
            if (m_SQLConn.State != System.Data.ConnectionState.Open)
            {
//                MessageBox.Show("Didn't Open!");
                return false;
            }
            return true;
        }

        public void initSolarSystemGraph(eveMap mapSolarSystems)
        {
            m_SystemLookupFromStation = new SqlCommand("select solarSystemID from ebs_DATADUMP.dbo.staStations where stationID = @stationID", m_SQLConn);
            m_SystemLookupFromStation.Parameters.Add("@stationID", SqlDbType.Int);
            using (SqlDataAdapter adapt = new SqlDataAdapter("select * from ebs_DATADUMP.dbo.mapSolarSystems", m_SQLConn))
            {
                adapt.Fill(m_EVEDumpDataSet, "Systems");
            }
            using (SqlDataAdapter adapt = new SqlDataAdapter("select * from ebs_DATADUMP.dbo.mapSolarSystemJumps", m_SQLConn))
            {
                adapt.Fill(m_EVEDumpDataSet, "Jumps");
            }
            m_EVEDumpDataSet.Relations.Add("outboundJumps", m_EVEDumpDataSet.Tables["Systems"].Columns["solarSystemID"],
                m_EVEDumpDataSet.Tables["Jumps"].Columns["fromSolarSystemID"]);
            foreach (DataRow tmpRow in m_EVEDumpDataSet.Tables["Systems"].Rows)
            {
                eveSolarSystem tmp = new eveSolarSystem(tmpRow);
                mapSolarSystems.Add(tmp.ID, tmp);
            }
        }

        /// <summary>
        /// delete all values in the scratch assets table, and rebuild it from the API
        /// </summary>
        public void loadAssets()
        {
            List<Asset> charAssets;
            charAssets = m_API.GetCharacterAssets();
            SqlConnection bulkConn = new SqlConnection(m_ConnectionString);
            bulkConn.Open();
            SqlDataAdapter myDataAdapter = new SqlDataAdapter("select * from evestuff.dbo.ScratchAssets", bulkConn);
            SqlCommandBuilder builder = new SqlCommandBuilder(myDataAdapter);
            SqlCommand deleter = new SqlCommand("delete from evestuff.dbo.ScratchAssets", bulkConn);
            deleter.ExecuteNonQuery();
            WebClient pricegetter = new WebClient();
            SqlCommand cmd = builder.GetInsertCommand(true);
            SqlTransaction trans = bulkConn.BeginTransaction();
            cmd.Transaction = trans;
            foreach (Asset anAsset in charAssets)
            {

                cmd.Parameters["@ItemID"].Value = anAsset.ItemID;
                cmd.Parameters["@StationID"].Value = anAsset.LocationID;
                //m_SystemLookupFromStation.Parameters.AddWithValue("@stationID", anAsset.LocationID);
                m_SystemLookupFromStation.Parameters["@stationID"].Value = anAsset.LocationID;
                int solarSystem = Convert.ToInt32(m_SystemLookupFromStation.ExecuteScalar());
                cmd.Parameters["@SolarSystemID"].Value = solarSystem;
                cmd.Parameters["@Quantity"].Value = anAsset.Quantity;
                cmd.Parameters["@TypeID"].Value = anAsset.TypeID;
                float stackValue = GetAssetValue(pricegetter, anAsset);
                cmd.Parameters["@Value"].Value = stackValue;
                //cmd.Parameters["@Value"].Value = anAsset.
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
            }
            cmd.Transaction.Commit();
            bulkConn.Close();
        }

        private static float GetAssetValue(WebClient pricegetter, Asset anAsset)
        {
            float Result;
            string priceQueryBase1 = "http://api.eve-central.com/api/marketstat?usesystem=30002053&typeid=";
            string priceQueryBase2 = "http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid=";
            Result = GetAssetValueImpl(pricegetter, anAsset, priceQueryBase1);
            if (Result == 0)
                Result = GetAssetValueImpl(pricegetter, anAsset, priceQueryBase2);
            return Result;
        }

        private static float GetAssetValueImpl(WebClient pricegetter, Asset anAsset, string priceQueryBase1)
        {
            string priceQuery = priceQueryBase1 + Convert.ToString(anAsset.TypeID);
            string XMLString = pricegetter.DownloadString(priceQuery);
            XmlDocument priceDoc = new XmlDocument();
            priceDoc.LoadXml(XMLString);
            XmlNode root = priceDoc.DocumentElement;
            XmlNode sell = root.SelectSingleNode("//sell/percentile");
            string sellPrice = sell.InnerText;
            float stackValue = (float)(Convert.ToDouble(sellPrice) * anAsset.Quantity);
            return stackValue;
        }

        public List<string> RegionNames()
        {
            List<string> Result = new List<string>();
            DataSet regData = new DataSet("RegionNames");
            string regionNamesQuery = 
                "select regionName from ebs_DATADUMP.dbo.mapRegions " +
                "where " +
	                "regionName not like 'A-%'" +
	                "and regionName not like 'B-%' " +
	                "and regionName not like 'C-%' " +
	                "and regionName not like 'D-%' " +
	                "and regionName not like 'E-%' " +
	                "and regionName not like 'F-%' " +
	                "and regionName not in ('UUA-F4', 'J7HZ-F', 'A821-A') " +
                "order by regionName";
            using (SqlDataAdapter adapt = new SqlDataAdapter(regionNamesQuery, m_SQLConn))
            {
                adapt.Fill(regData, "RegionNames");
            }
            foreach (DataRow regRow in regData.Tables["RegionNames"].Rows)
            {
                Result.Add(Convert.ToString(regRow["regionName"]));
            }
            return Result;
        }
    }
}
